# 数据库连接管理器（支持连接池）
import sqlite3
import threading

import mysql.connector

from configs.basic_config import DB_PATH


class DatabaseManager:
    _instances = {}
    _lock = threading.Lock()

    def __new__(cls, db_type="sqlite", **kwargs):
        key = f"{db_type}_{kwargs.get('database', 'default')}"
        if key not in cls._instances:
            with cls._lock:
                if key not in cls._instances:
                    cls._instances[key] = super().__new__(cls)
        return cls._instances[key]

    def __init__(self, db_type="sqlite", **kwargs):
        if not hasattr(self, '_initialized'):
            self.db_type = db_type
            self.connection_params = kwargs
            self._initialized = True

            # 初始化连接池
            if db_type == "mysql":
                self.pool = mysql.connector.pooling.MySQLConnectionPool(
                    pool_name="mcp_hotel_pool",
                    pool_size=5,
                    pool_reset_session=True,
                    **kwargs
                )
            else:
                # SQLite 使用线程本地存储
                self.local = threading.local()

    def get_connection(self):
        if self.db_type == "mysql":
            return self.pool.get_connection()
        else:
            # SQLite 使用线程本地连接
            # 检查连接是否仍然有效，如果无效或不存在则重新创建
            if (not hasattr(self.local, 'connection') or 
                self.local.connection is None or 
                not self._is_sqlite_connection_valid(self.local.connection)):
                db_path = self.connection_params.get('database', DB_PATH)
                self.local.connection = sqlite3.connect(db_path, check_same_thread=False)
                self.local.connection.row_factory = sqlite3.Row
                self.local.connection.execute("PRAGMA foreign_keys = ON")
            return self.local.connection

    def _is_sqlite_connection_valid(self, connection):
        """检查 SQLite 连接是否仍然有效"""
        try:
            connection.execute("SELECT 1")
            return True
        except:
            return False

    def close_connection(self):
        """关闭 SQLite 连接（MySQL 连接由连接池管理）"""
        if self.db_type == "sqlite" and hasattr(self.local, 'connection'):
            if self.local.connection:
                try:
                    self.local.connection.close()
                except:
                    pass  # 忽略关闭时的错误
                self.local.connection = None

    def reset_connection(self):
        """重置 SQLite 连接"""
        if self.db_type == "sqlite":
            self.close_connection()